/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package dao;

import dto.TourCategory;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import util.DBUtil;

/**
 *
 * @author lab
 */
public class TourCateDAO {

    String SQL_CREATE_TOURCATE = "INSERT INTO [TourCate]( CateName, Description) VALUES(?, ?)";
    String SQL_READ_ALL_TOURCATE = "SELECT * FROM [TourCate]";
    String SQL_UPDATE_TOURCATE = "UPDATE [TourCate] SET CateName = ?,Description = ? WHERE TourCateID = ? ";
    String SQl_DELETE_TOURCATE = "DELETE from [TourCate] WHERE TourCateID = ?";
    String SQL_READ_TOURCATE_BY_ID = "SELECT * FROM [TourCate]";
    private Connection conn = null;
    private PreparedStatement pst = null;
    private ResultSet rs = null;
    public String mesage = "";

    public TourCateDAO() {
    }

    public boolean  createTourCategory(TourCategory tourCategory) {
        boolean result = false;
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_CREATE_TOURCATE);
            pst.setString(1, tourCategory.getCateName());
            pst.setString(2, tourCategory.getDescription());
            if (pst.executeUpdate() > 0) {
                mesage = "Create Tour Category successfully!";
                result = true;
            } else {
                mesage = "Create Tour Category unsuccessfully!";
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourCateDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtil.closeConn(conn);
        }
        return result;
    }

    public ArrayList<TourCategory> getAllTourCate() {
        ArrayList<TourCategory> tourCates = new ArrayList<TourCategory>();
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_READ_ALL_TOURCATE);
            rs = pst.executeQuery();
            while (rs.next()) {
                TourCategory tourCate = new TourCategory();
                tourCate.setTourCateID(rs.getInt("TourCateID"));
                tourCate.setCateName(rs.getString("CateName"));
                tourCate.setDescription(rs.getString("Description"));
                tourCates.add(tourCate);
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourCateDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

            DBUtil.closeConn(conn);
        }
        return tourCates;
    }

    public boolean  updateTourCate(TourCategory tourCategory) {
        boolean result = false;
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_UPDATE_TOURCATE);
            pst.setString(1, tourCategory.getCateName());
            pst.setString(2, tourCategory.getDescription());
            pst.setInt(3, tourCategory.getTourCateID());
            if (pst.executeUpdate() > 0) {
                mesage = "Update Tour Category successfully!";
                result = true;
            } else {
                mesage = "Update Tour Category unsuccessfully!";
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourCateDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            DBUtil.closeConn(conn);
        }
        return result;
    }

    public boolean  deleteTourCate(int tourCateID) {
        boolean result = false;
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQl_DELETE_TOURCATE);
            pst.setInt(1, tourCateID);
            if (pst.executeUpdate() > 0) {
                mesage = "Delete successfully!";
                result = true;
            } else {
                mesage = "Delete unsuccessfully!";
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourCateDAO.class.getName()).log(Level.SEVERE, null, ex);
            mesage = "Delete failure!";
        } finally {

            DBUtil.closeConn(conn);
        }
        return result;
    }

    public TourCategory getTourCateByID(int tourCateID) {
        TourCategory tourCate = null;
        try {
            conn = DBUtil.getConn();
            pst = conn.prepareStatement(SQL_READ_TOURCATE_BY_ID);
            pst.setInt(1, tourCateID);
            rs = pst.executeQuery();
            if (rs.next()) {
                tourCate = new TourCategory();
                tourCate.setTourCateID(rs.getInt("TourCateID"));
                tourCate.setCateName(rs.getString("CateName"));
                tourCate.setDescription(rs.getString("Description"));
            }
            pst.close();
        } catch (SQLException ex) {
            Logger.getLogger(TourCateDAO.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

            DBUtil.closeConn(conn);
        }
        return tourCate;
    }
}
